package love.yuanmengrobot.als.business.dao.impl;
/*
 *   @Studio:CHJY · 源梦科技
 *   @Author:COOLIGHT
 *   @Ide:IntelliJ IDEA
 *   @Creation time:2021/4/23 0023 19:37
 */

import love.yuanmengrobot.als.business.dao.ReadStudentScoreDao;
import love.yuanmengrobot.als.business.javaBean.ScoreView;
import love.yuanmengrobot.als.business.javaBean.Student;
import love.yuanmengrobot.als.utilPublic.JdbcUtil;
import love.yuanmengrobot.als.utilPublic.SqlString;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellBase;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;

/* 功能: 读取excel,导入数据到数据库,建议使用xls格式*/
public class ReadStudentScoreDaoImpl implements ReadStudentScoreDao, Runnable {
    private ScoreView scoreView;
    private String course_term;//学期
    private String stu_no;//学号
    private String stu_name;//姓名
    private String major_name;//专业
    private int grade;//年级
    private String clazz;//班级
    private String course_code;//课程代码
    private String course_id;//课号
    private String course_name;//课程名称
    private String score;//成绩
    private double credit;//学分
    private String test_type;//考试类别
    private String score_type;//成绩类别
    private String course_type;//课程性质
    private String path;//文件路径

    @Override
    public void run() {
        getExcel();
    }

    public ReadStudentScoreDaoImpl() {
    }

    private ReadStudentScoreDaoImpl(String path) {
        this.path = path;
    }

    /**
     * 读取学生成绩excel
     * @param path 文件路径
     */
    public void getAllInfo(String path) {
        Runnable r = new ReadStudentScoreDaoImpl(path);
        Thread thread = new Thread(r);
        thread.start();
    }

    private void getExcel() {
        try {
            InputStream inputStream = new FileInputStream(path);
            if (path.endsWith(".xls")) {
                HSSFWorkbook xw = new HSSFWorkbook(inputStream);
                HSSFSheet sheet = xw.getSheetAt(0);//工作簿1
                getRowInfo(sheet);
            } else {
                XSSFWorkbook xw = new XSSFWorkbook(inputStream);
                XSSFSheet sheet = xw.getSheetAt(0);//工作簿1
                getRowInfo(sheet);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取工作簿中所有行
     * @param sheet 工作簿
     */
    private void getRowInfo(Sheet sheet) {
        for (int j = 2; j <= sheet.getLastRowNum(); j++) {
            if (getPath().endsWith(".xls")) {
                HSSFRow row = (HSSFRow) sheet.getRow(j);//读取行
                getCellInfo(row);
            } else {
                XSSFRow row = (XSSFRow) sheet.getRow(j);
                getCellInfo(row);
            }
        }
    }

    /**
     * 读取一行中的所有列
     * @param row 行
     */
    private void getCellInfo(Row row) {
        for (int k = 0; k <= row.getLastCellNum(); k++) {
            if (getPath().endsWith(".xls")) {
                HSSFCell cell = (HSSFCell) row.getCell(k);
                getData(cell, k);
            } else {
                XSSFCell cell = (XSSFCell) row.getCell(k);
                getData(cell, k);
            }
        }
        insertData();
    }

    /**
     * 读取列中的数据
     * @param cell 单元
     * @param k 列号
     */
    private void getData(CellBase cell, int k) {
        switch (k) {
            case 1:
                scoreView.setCourse_term(cell.getStringCellValue());
                break;
            case 2:
                scoreView.setStu_no(cell.getStringCellValue());
                break;
            case 3:
                scoreView.setStu_name(cell.getStringCellValue());
                break;
            case 4:
                scoreView.setMajor_name(cell.getStringCellValue());
                break;
            case 5:
                scoreView.setGrade(Integer.parseInt(cell.getStringCellValue()));
                break;
            case 6:
                scoreView.setClazz(cell.getStringCellValue());
                break;
            case 7:
                scoreView.setCourse_code(cell.getStringCellValue());
                break;
            case 8:
                scoreView.setCourse_id(cell.getStringCellValue());
                break;
            case 9:
                scoreView.setCourse_name(cell.getStringCellValue());
                break;
            case 12:
                scoreView.setScore(cell.getStringCellValue());
                break;
            case 13:
                scoreView.setCredit(Double.parseDouble(cell.getStringCellValue()));
                break;
            case 14:
                scoreView.setTest_type(cell.getStringCellValue());
                break;
            case 15:
                scoreView.setScore_type(cell.getStringCellValue());
                break;
            case 17:
                scoreView.setCourse_type(cell.getStringCellValue());
                break;
            default:
        }
    }

    /**
     * 连接数据库插入数据
     */
    private void insertData() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtil.getConnection();
            pstmt = conn.prepareStatement(SqlString.selectMajorByMajorName(getMajor_name()));

            rs = pstmt.executeQuery();//拿到专业数据,判断专业是否存在

            if (!rs.next()) {
                pstmt = conn.prepareStatement(SqlString.insertDataIntoMajor(getMajor_name()));
                if (pstmt.executeUpdate() == 1) {
                    System.out.println("添加专业成功");//专业不存在,添加专业
                } else {
                    System.out.println("添加专业失败");
                }
            }
            pstmt = conn.prepareStatement(SqlString.selectStudentByStuNo(getStu_no()));
            rs = pstmt.executeQuery();//拿到学生数据,判断学生是否存在

            if (!rs.next()) {
                pstmt = conn.prepareStatement(SqlString.insertDataIntoStudent(scoreView));
                if (pstmt.executeUpdate() == 1) {
                    System.out.println("添加学生成功");//学生不存在,添加学生
                } else {
                    System.out.println("添加学生失败");
                }
            }
            pstmt = conn.prepareStatement(SqlString.selectScoreByStuNo(getStu_no(), getCourse_id()));
            rs = pstmt.executeQuery();//拿到学生成绩信息,判断学生成绩是否存在

            if (!rs.next()) {
                pstmt = conn.prepareStatement(SqlString.insertDataIntoScore(scoreView));
                if (pstmt.executeUpdate() == 1) {
                    System.out.println("添加成绩成功");//学生成绩不存在,添加学生成绩
                } else {
                    System.out.println("添加成绩失败");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(rs,pstmt,conn);
        }
    }

    private String getCourse_term() {
        return course_term;
    }

    private void setCourse_term(String course_term) {
        this.course_term = course_term;
    }

    private String getStu_no() {
        return stu_no;
    }

    private void setStu_no(String stu_no) {
        this.stu_no = stu_no;
    }

    private String getStu_name() {
        return stu_name;
    }

    private void setStu_name(String stu_name) {
        this.stu_name = stu_name;
    }

    private String getMajor_name() {
        return major_name;
    }

    private void setMajor_name(String major_name) {
        this.major_name = major_name;
    }

    private int getGrade() {
        return grade;
    }

    private void setGrade(String grade) {
        this.grade = Integer.parseInt(grade);
    }

    private String getClazz() {
        return clazz;
    }

    private void setClazz(String clazz) {
        this.clazz = clazz;
    }

    private String getCourse_code() {
        return course_code;
    }

    private void setCourse_code(String course_code) {
        this.course_code = course_code;
    }

    private String getCourse_id() {
        return course_id;
    }

    private void setCourse_id(String course_id) {
        this.course_id = course_id;
    }

    private String getCourse_name() {
        return course_name;
    }

    private void setCourse_name(String course_name) {
        this.course_name = course_name;
    }

    private String getScore() {
        return score;
    }

    private void setScore(String score) {
        this.score = score;
    }

    private double getCredit() {
        return credit;
    }

    private void setCredit(String credit) {
        this.credit = Double.parseDouble(credit);
    }

    private String getTest_type() {
        return test_type;
    }

    private void setTest_type(String test_type) {
        this.test_type = test_type;
    }

    private String getScore_type() {
        return score_type;
    }

    private void setScore_type(String score_type) {
        this.score_type = score_type;
    }

    private String getCourse_type() {
        return course_type;
    }

    private void setCourse_type(String course_type) {
        this.course_type = course_type;
    }

    private String getPath() {
        return path;
    }

    public ScoreView getScoreView() {
        return scoreView;
    }

    public void setScoreView(ScoreView scoreView) {
        this.scoreView = scoreView;
    }
}
